﻿using System;
using System.Data;
using System.Data.Common;
using System.Globalization;
using System.IO;
using System.Xml;
using System.Xml.Serialization;
using Microsoft.Practices.EnterpriseLibrary.Data;

namespace ConfExpert.Utility.Data
{
    public class DataManager
    {
        static Database database = null;

        public DataManager(string cs)
        {
            database = new Microsoft.Practices.EnterpriseLibrary.Data.Sql.SqlDatabase(cs);
        }

        public static StringWriter Datatable2Xml(DataTable dt)
        {
            try
            {
                XmlSerializerNamespaces ns = new XmlSerializerNamespaces();
                ns.Add("", "");

                XmlSerializer xmlr = new XmlSerializer(typeof(DataTable));    //serialize the object
                StringWriter sw = new StringWriter(CultureInfo.InvariantCulture);
                XmlTextWriter tw = new MyXmlTextWriter(sw);
                xmlr.Serialize(tw, dt, ns);
                return sw;
            }
            catch (Exception Ex)
            {
                throw Ex;
            }
        }

        public class MyXmlTextWriter : System.Xml.XmlTextWriter
        {
            public MyXmlTextWriter(System.IO.TextWriter w)
                : base(w)
            {
                //Formatting = System.Xml.Formatting.Indented;
            }

            public override void WriteStartDocument()
            {
                //to suppress
            }
        }

        public DataSet ExecuteDs(string storedProcedureName, params object[] parameterValues)
        {
            // new SqlConnection(Properties.Settings.Default["ConfExpertConnectionString"].ToString()
            //sqlConnection.ConnectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString.Trim();
            using (DbConnection sqlConnection = database.CreateConnection())
            {
                using (DbCommand sqlCommand = database.GetStoredProcCommand(storedProcedureName, parameterValues))
                {
                    sqlCommand.CommandType = CommandType.StoredProcedure;
                    sqlCommand.CommandTimeout = 90;
                    sqlCommand.Connection = sqlConnection;

                    try
                    {
                        DataSet ds = new DataSet();
                        ds.EnforceConstraints = false;
                        DbDataAdapter sqlda = database.GetDataAdapter();
                        sqlda.SelectCommand = sqlCommand;
                        sqlda.Fill(ds);
                        return ds;
                    }
                    catch (Exception Ex)
                    {
                        throw (Ex);
                    }

                }
            }
        }

        public DataTable ExecuteDt(string storedProcedureName, params object[] parameterValues)
        {
            using (DbConnection sqlConnection = database.CreateConnection())
            {
                using (DbCommand sqlCommand = database.GetStoredProcCommand(storedProcedureName, parameterValues))
                {
                    sqlCommand.CommandType = CommandType.StoredProcedure;
                    sqlCommand.CommandTimeout = 90;
                    sqlCommand.Connection = sqlConnection;

                    try
                    {
                        DataTable dt = new DataTable("Table");
                        DbDataAdapter sqlda = database.GetDataAdapter();
                        sqlda.SelectCommand = sqlCommand;
                        dt.BeginLoadData();
                        sqlda.Fill(dt);
                        dt.EndLoadData();
                        return dt;
                    }
                    catch (Exception Ex)
                    {
                        throw (Ex);
                    }

                }
            }
        }

        public object ExecuteSc(string SP, params object[] prm)
        {
            using (DbConnection sqlConnection = database.CreateConnection())
            {
                using (DbCommand sqlCommand = database.GetStoredProcCommand(SP, prm))
                {
                    try
                    {
                        sqlConnection.Open();
                        sqlCommand.CommandType = CommandType.StoredProcedure;
                        sqlCommand.CommandTimeout = 90;
                        sqlCommand.Connection = sqlConnection;

                        return sqlCommand.ExecuteScalar();
                    }
                    catch (Exception Ex)
                    {
                        throw (Ex);
                    }
                }
            }
             
        }

        public object ExecuteNq(string SP, params object[] prm)
        {
            using (DbConnection sqlConnection = database.CreateConnection())
            {
                using (DbCommand sqlCommand = database.GetStoredProcCommand(SP, prm))
                {
                    try
                    {
                        sqlConnection.Open();
                        sqlCommand.CommandType = CommandType.StoredProcedure;
                        sqlCommand.CommandTimeout = 90;
                        sqlCommand.Connection = sqlConnection;

                        return sqlCommand.ExecuteNonQuery();
                    }
                    catch (Exception Ex)
                    {
                        throw (Ex);
                    }
                }
            }

        }


    }
}
